﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CarolLib;
using CarolLib.Extension;
using DCS.Business.Model;
using DCS.Business.Model.PublishUsage;
using System.Linq;
using CarolLib.Core.Helper;

namespace DCS.DataProvider
{
    public class SqlDataProvider : IDataProvider
    {
        #region Conn
         
        private static string connectionString;

        private SqlConnection GetSqlConnection()
        {
            return new SqlConnection(connectionString);
        }

        public SqlDataProvider()
        {
            try
            {
                connectionString = ConfigurationManager.ConnectionStrings["DCSDB"].ConnectionString;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        #endregion

        #region DCSFile

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        //public List<string> GetRecentFileNames(int recentDays)
        //{
        //    List<string> names = null;
        //    using (SqlConnection conn = GetSqlConnection())
        //    {
        //        using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetRecentFileNames", new SqlParameter("@DayCount", recentDays)))
        //        {
        //            names = SqlHelper.PopulateReadersToIds<string>(dr, "RemoteFileName");
        //        }
        //    }
        //    return names;
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="recentDays"></param>
        /// <returns></returns>
        public Dictionary<string, DateTime> GetRecentFileNames(int recentDays)
        {
            Dictionary<string, DateTime> names = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetRecentFileNames", new SqlParameter("@DayCount", recentDays)))
                {
                    names = SqlHelper.PopulateReadersToDic<string, DateTime>(dr, "RemoteFileName", "CreateTime");
                }
                conn.Close();
            }
            return names;
        }

        /// <summary>
        /// 删除文件列表
        /// </summary>
        /// <param name="fileList"></param>
        public void DeleteExitFileList(List<string> fileList, string messageType)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_DeleteFileList",
                                          new SqlParameter("@FileNameList", SqlHelper.ConvertIdsToXML<string>("e", fileList.ToArray())),
                                          new SqlParameter("@MessageType", messageType));

                conn.Close();

            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dCSFile"></param>
        public DCSFile CreateDCSFile(DCSFile dCSFile)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter spFileId = new SqlParameter("@FileId", SqlDbType.Int);
                spFileId.Direction = ParameterDirection.Output;
                spFileId.Value = dCSFile.FileId;

                SqlHelper.ExecuteNonQuery(conn, "up_CreateDCSFile",
                    spFileId
                    , new SqlParameter("@RemoteFileName", dCSFile.RemoteFileName)
                    , new SqlParameter("@RemotePath", dCSFile.RemotePath)
                    , new SqlParameter("@LocalFileName", dCSFile.LocalFileName)
                    , new SqlParameter("@LocalPath", dCSFile.LocalPath)
                    , new SqlParameter("@Size", dCSFile.Size)
                    , new SqlParameter("@MessageType", dCSFile.MessageType)
                    , new SqlParameter("@Status", dCSFile.Status)
                    , new SqlParameter("@CreateTime", dCSFile.CreateTime)
                    , new SqlParameter("@DetectTime", dCSFile.DetectTime)
                    , new SqlParameter("@DownloadTime", dCSFile.DownloadTime)
                    , new SqlParameter("@ParseStartTime", dCSFile.ParseStartTime)
                    , new SqlParameter("@ParseFinishTime", dCSFile.ParseFinishTime)
                    , new SqlParameter("@ResultId", dCSFile.ResultId)
                );
                dCSFile.FileId = (int)spFileId.Value;
                return dCSFile;
            }


        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dCSFile"></param>
        public void UpdateDCSFile(DCSFile dCSFile)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateDCSFile",
                    new SqlParameter("@FileId", dCSFile.FileId)
                    , new SqlParameter("@RemoteFileName", dCSFile.RemoteFileName)
                    , new SqlParameter("@RemotePath", dCSFile.RemotePath)
                    , new SqlParameter("@LocalFileName", dCSFile.LocalFileName)
                    , new SqlParameter("@LocalPath", dCSFile.LocalPath)
                    , new SqlParameter("@Size", dCSFile.Size)
                    , new SqlParameter("@MessageType", dCSFile.MessageType)
                    , new SqlParameter("@Status", dCSFile.Status)
                    , new SqlParameter("@CreateTime", dCSFile.CreateTime)
                    , new SqlParameter("@DetectTime", dCSFile.DetectTime)
                    , new SqlParameter("@DownloadTime", dCSFile.DownloadTime)
                    , new SqlParameter("@ParseStartTime", dCSFile.ParseStartTime)
                    , new SqlParameter("@ParseFinishTime", dCSFile.ParseFinishTime)
                    , new SqlParameter("@ResultId", dCSFile.ResultId)
                );
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileIds"></param>
        /// <param name="dCSFiles"></param>
        public List<DCSFile> GetDCSFilesByMessageType(string messageType, FileStatus status, int rowCount, FileStatus lockStatus, bool fullMatch)
        {
            List<DCSFile> list = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetDCSFilesByMessageType",
                        new SqlParameter("@MessageType", messageType),
                        new SqlParameter("@Status", status),
                        new SqlParameter("@RowCount", rowCount),
                        new SqlParameter("@LockStatus", lockStatus),
                        new SqlParameter("@FullMatch", fullMatch)
                    ))
                {
                    if (dr.HasRows)
                    {
                        list = DTOHelper.CreateModelList<DCSFile>(dr);
                    }
                    else
                    {
                        return new List<DCSFile>();
                    }
                    dr.Close();
                    conn.Close();
                }
            }
            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="batchSize"></param>
        /// <param name="MessageType"></param>
        /// <returns></returns>
        public List<DCSFile> GetDCSFilesGroupByMessageType(string messageType, FileStatus status, int groupCount, FileStatus lockStatus = 0)
        {
            List<DCSFile> list = null;
            using (SqlConnection conn = GetSqlConnection())
            { 
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetDCSFilesGroupByMessageType",
                        new SqlParameter("@MessageType", messageType),
                        new SqlParameter("@Status", status),
                        new SqlParameter("@GroupCount", groupCount),
                        new SqlParameter("@LockStatus", lockStatus)
                    ))
                {
                    if (dr.HasRows)
                    {
                        list = DTOHelper.CreateModelList<DCSFile>(dr);
                    }
                    else
                    {
                        return new List<DCSFile>();
                    }
                    dr.Close();
                    conn.Close();
                }
            }
            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="files"></param>
        public void BatchUpdateDCSFilesStatus(int[] ids, FileStatus status)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_BatchUpdateDCSFilesStatus",
                        new SqlParameter("@Ids", SqlHelper.ConvertIdsToXML<int>("e", ids)),
                        new SqlParameter("@Status", status)
                    ))
                {
                    dr.Close();
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="batchSize"></param>
        /// <param name="MessageType"></param>
        /// <returns></returns>
        [Obsolete]
        public List<DCSFile> GetFilesToParse(int batchSize, string messageType)
        {
            List<DCSFile> list = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetFilesToParse",
                        new SqlParameter("@MessageType", messageType),
                        new SqlParameter("@BatchSize", batchSize)
                    ))
                {
                    if (dr.HasRows)
                    {
                        list = DTOHelper.CreateModelList<DCSFile>(dr);
                    }
                    else
                    {
                        return new List<DCSFile>();
                    }
                    dr.Close();
                    conn.Close();
                }
            }
            return list;
        }

        #endregion

        #region LDMMessage

        /// <summary>
        /// 
        /// </summary>
        /// <param name="lDMMessage"></param>
        public LDMMessage CreateLDMMessage(LDMMessage lDMMessage)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateLDMMessage",
                      new SqlParameter("@Carrier", lDMMessage.Carrier)
                    , new SqlParameter("@FlightNo", lDMMessage.FlightNo)
                    , new SqlParameter("@PDate", lDMMessage.PDate)
                    , new SqlParameter("@PTime", lDMMessage.PTime)
                    , new SqlParameter("@DeptAirport", lDMMessage.DeptAirport)
                    , new SqlParameter("@ArrAirport", lDMMessage.ArrAirport)
                    , new SqlParameter("@RegNo", lDMMessage.RegNo)
                    , new SqlParameter("@PaxLayout", lDMMessage.PaxLayout)
                    , new SqlParameter("@Crew", lDMMessage.Crew)
                    , new SqlParameter("@Adult", lDMMessage.Adult)
                    , new SqlParameter("@Child", lDMMessage.Child)
                    , new SqlParameter("@Baby", lDMMessage.Baby)
                    , new SqlParameter("@BagWeight", lDMMessage.BagWeight)
                    , new SqlParameter("@LoadTotal", lDMMessage.LoadTotal)
                    , new SqlParameter("@LoadInCompartment", lDMMessage.LoadInCompartment)
                    , new SqlParameter("@PaxF", lDMMessage.PaxF)
                    , new SqlParameter("@PaxC", lDMMessage.PaxC)
                    , new SqlParameter("@PaxY", lDMMessage.PaxY)
                    , new SqlParameter("@PadF", lDMMessage.PadF)
                    , new SqlParameter("@PadC", lDMMessage.PadC)
                    , new SqlParameter("@PadY", lDMMessage.PadY)
                    , new SqlParameter("@AdditionalInfo", lDMMessage.AdditionalInfo)
                    , new SqlParameter("@RefFileId", lDMMessage.RefFileId)
                    , new SqlParameter("@LspContent", lDMMessage.LspContent)
                );
                return lDMMessage;
            }
        }

        public LDMMessage CreateDistinctLDMMessage(LDMMessage lDMMessage)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreateDistinctLDMMessage",
                      new SqlParameter("@Carrier", lDMMessage.Carrier)
                    , new SqlParameter("@FlightNo", lDMMessage.FlightNo)
                    , new SqlParameter("@PDate", lDMMessage.PDate)
                    , new SqlParameter("@PTime", lDMMessage.PTime)
                    , new SqlParameter("@DeptAirport", lDMMessage.DeptAirport)
                    , new SqlParameter("@ArrAirport", lDMMessage.ArrAirport)
                    , new SqlParameter("@RegNo", lDMMessage.RegNo)
                    , new SqlParameter("@PaxLayout", lDMMessage.PaxLayout)
                    , new SqlParameter("@Crew", lDMMessage.Crew)
                    , new SqlParameter("@Adult", lDMMessage.Adult)
                    , new SqlParameter("@Child", lDMMessage.Child)
                    , new SqlParameter("@Baby", lDMMessage.Baby)
                    , new SqlParameter("@BagWeight", lDMMessage.BagWeight)
                    , new SqlParameter("@LoadTotal", lDMMessage.LoadTotal)
                    , new SqlParameter("@LoadInCompartment", lDMMessage.LoadInCompartment)
                    , new SqlParameter("@PaxF", lDMMessage.PaxF)
                    , new SqlParameter("@PaxC", lDMMessage.PaxC)
                    , new SqlParameter("@PaxY", lDMMessage.PaxY)
                    , new SqlParameter("@PadF", lDMMessage.PadF)
                    , new SqlParameter("@PadC", lDMMessage.PadC)
                    , new SqlParameter("@PadY", lDMMessage.PadY)
                    , new SqlParameter("@AdditionalInfo", lDMMessage.AdditionalInfo)
                    , new SqlParameter("@RefFileId", lDMMessage.RefFileId)
                    , new SqlParameter("@LspContent", lDMMessage.LspContent)
                );
                return lDMMessage;
            }
        }


        public List<FIDSLDMMsg> GetNewLDMMsg(decimal MaxID)
        {
            List<FIDSLDMMsg> result = new List<FIDSLDMMsg>();
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetNewLDMMsg", new SqlParameter("@MaxID", MaxID)))
                {
                    if (dr.HasRows)
                    {
                        //result = DTOHelper.CreateModelList<FIDSLDMMsg>(dr);
                        while (dr.Read())
                        {
                            result.Add(new FIDSLDMMsg(dr));
                        }
                    }
                }
            }
            return result;
        }

        public List<FlightInfo> GetFlightInfoByPara(DateTime PDate, string Carrier, string FlightNo)
        {
            List<FlightInfo> result = new List<FlightInfo>();
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAllFlightInfo", new SqlParameter("@PDate", PDate)
                                                                                         , new SqlParameter("Carrier", Carrier)
                                                                                         , new SqlParameter("FlightNo", FlightNo)
                                                                                         ))
                {
                    if (dr.HasRows)
                    {
                        //result = DTOHelper.CreateModelList<FlightInfo>(dr);
                        while (dr.Read())
                        { 
                         result.Add(new FlightInfo(dr));
                        }
                    }
                }
            }
            return result;
        }

        #endregion

        #region PRLMessage

        public PRLMessage CreatePRLMessage(PRLMessage pRLMessage)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreatePRLMessage",
                      new SqlParameter("@Carrier", pRLMessage.Carrier)
                    , new SqlParameter("@FlightNo", pRLMessage.FlightNo)
                    , new SqlParameter("@PDate", pRLMessage.PDate)
                    , new SqlParameter("@DeptAirport", pRLMessage.DeptAirport)
                    , new SqlParameter("@ArrAirport", pRLMessage.ArrAirport)
                    , new SqlParameter("@CabinNo", pRLMessage.CabinNo)
                    , new SqlParameter("@PName", pRLMessage.PName)
                    , new SqlParameter("@PNR", pRLMessage.PNR)
                    , new SqlParameter("@Seat", pRLMessage.Seat)
                    , new SqlParameter("@BN", pRLMessage.BN)
                    , new SqlParameter("@GATE", pRLMessage.GATE)
                    , new SqlParameter("@WK", pRLMessage.WK)
                    , new SqlParameter("@Package", pRLMessage.Package)
                    , new SqlParameter("@ShareCode", pRLMessage.ShareCode)
                    , new SqlParameter("@OutBound", pRLMessage.OutBound)
                    , new SqlParameter("@FQTV", pRLMessage.FQTV)
                    , new SqlParameter("@FQTR", pRLMessage.FQTR)
                    , new SqlParameter("@FQTS", pRLMessage.FQTS)
                    , new SqlParameter("@FQTU", pRLMessage.FQTU)
                    , new SqlParameter("@HC", pRLMessage.HC)
                    , new SqlParameter("@TKNE", pRLMessage.TKNE)
                    , new SqlParameter("@FOID", pRLMessage.FOID)
                    , new SqlParameter("@PSPT", pRLMessage.PSPT)
                    , new SqlParameter("@CNIN", pRLMessage.CNIN)
                    , new SqlParameter("@CKIN", pRLMessage.CKIN)
                    , new SqlParameter("@PSM", pRLMessage.PSM)
                    , new SqlParameter("@SPML", pRLMessage.SPML)
                    , new SqlParameter("@SSR", pRLMessage.SSR)
                    , new SqlParameter("@IsVip", pRLMessage.IsVip)
                    , new SqlParameter("@RefFileId", pRLMessage.RefFileId)
                );


                return pRLMessage;
            }
        }

        #endregion

        #region PFSMessage

        public PFSMessage CreatePFSMessage(PFSMessage pFSMessage)
        {
            Guid newId;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_CreatePFSMessage",
                      new SqlParameter("@Carrier", pFSMessage.Carrier)
                    , new SqlParameter("@FlightNo", pFSMessage.FlightNo)
                    , new SqlParameter("@PDate", pFSMessage.PDate)
                    , new SqlParameter("@DeptAirport", pFSMessage.DeptAirport)
                    , new SqlParameter("@ArrAirport", pFSMessage.ArrAirport)
                    , new SqlParameter("@PaxLayout", pFSMessage.PaxLayout)
                    , new SqlParameter("@BoardedPassenger", pFSMessage.BoardedPassenger)
                    , new SqlParameter("@PadPassenger", pFSMessage.PadPassenger)
                    , new SqlParameter("@Baby", pFSMessage.Baby)
                    , new SqlParameter("@TransitPassenger", pFSMessage.TransitPassenger)
                    , new SqlParameter("@TransitBaby", pFSMessage.TransitBaby)
                    , new SqlParameter("@SharedNo", pFSMessage.SharedNo)
                    , new SqlParameter("@RefFileId", pFSMessage.RefFileId)
                ))
                {
                    dr.Read();
                    newId = new Guid(dr[0].ToString());
                }
            }

            pFSMessage.PFSPassengers.ForEach(p =>
            {
                p.MessageId = newId;
                CreatePFSPassenger(p);
            });

            return pFSMessage;
        }

        public PFSPassenger CreatePFSPassenger(PFSPassenger pFSPassenger)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_CreatePFSPassenger",
                     new SqlParameter("@MessageId", pFSPassenger.MessageId)
                    , new SqlParameter("@StatusCode", pFSPassenger.StatusCode)
                    , new SqlParameter("@Class", pFSPassenger.Class)
                    , new SqlParameter("@ClassNum", pFSPassenger.ClassNum)
                    , new SqlParameter("@PName", pFSPassenger.PName)
                    , new SqlParameter("@PNR", pFSPassenger.PNR)
                    , new SqlParameter("@FFCard", pFSPassenger.FFCard)
                );
            }

            return pFSPassenger;
        }

        #endregion

        #region SSRCode

        public Dictionary<string, SSRCode> GetSPMLs()
        {
            Dictionary<string, SSRCode> dic = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetSSRCodes",
                        new SqlParameter("@CodeType", "SPML")
                    ))
                {
                    if (dr.HasRows)
                    {
                        dic = DTOHelper.GetDictionary<string, SSRCode>("Code", dr);
                    }
                    else
                    {
                        return new Dictionary<string, SSRCode>();
                    }
                    dr.Close();
                    conn.Close();
                }
            }
            return dic;
        }

        public Dictionary<string, SSRCode> GetSSRs()
        {
            Dictionary<string, SSRCode> dic = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetSSRCodes",
                        new SqlParameter("@CodeType", "SSR")
                    ))
                {
                    if (dr.HasRows)
                    {
                        dic = DTOHelper.GetDictionary<string, SSRCode>("Code", dr);
                    }
                    else
                    {
                        return new Dictionary<string, SSRCode>();
                    }
                    dr.Close();
                    conn.Close();
                }
            }
            return dic;
        }

        #endregion

        #region Monitor Usage

        public List<DCSFileSummary> GetDCSFileSummary(bool groupByDate)
        {
            List<DCSFileSummary> result = null;

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetDCSFileSummary"
                    , new SqlParameter("@GroupByDate", groupByDate)
                    ))
                {
                    if (dr.HasRows)
                    {
                        result = DTOHelper.CreateModelList<DCSFileSummary>(dr);
                    }
                    else
                    {
                        return new List<DCSFileSummary>();
                    }
                    dr.Close();
                    conn.Close();
                }
            }
            return result;
        }

        public List<KeyValuePair<string, int>> GetAllTablesRowCount()
        {
            List<KeyValuePair<string, int>> result = new List<KeyValuePair<string, int>>();

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetAllTablesRowCount"
                    ))
                {
                    while (dr.Read())
                    {
                        result.Add(new KeyValuePair<string, int>(dr["TableName"].ToString(), dr["RowCount"].ToString().TryToInt()));
                    }
                    dr.Close();
                    conn.Close();
                }
            }
            return result;
        }

        public Dictionary<string, FileStatus> GetRecentFileStatus(int recentDays, string messageType)
        {
            Dictionary<string, FileStatus> result = new Dictionary<string, FileStatus>();

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetRecentFileStatus"
                    , new SqlParameter("@MessageType", messageType)
                    , new SqlParameter("@DayCount", recentDays)
                    ))
                {
                    while (dr.Read())
                    {
                        result.Add(dr["RemoteFileName"].ToString(), (FileStatus)int.Parse(dr["Status"].ToString()));
                    }
                    dr.Close();
                    conn.Close();
                }
            }

            return result;
        }

        #endregion

        #region Common Usage

        public List<T> QueryModelList<T>(string sql)
        {
            List<T> result = null;
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, sql, CommandType.Text))
                {
                    result = DTOHelper.CreateModelList<T>(dr);
                }
            }
            return result;
        }

        public List<PRLMessage> QueryTripHistory(string sql)
        {
            List<PRLMessage> result = new List<PRLMessage>();
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_QueryTripHistory", new SqlParameter("@WhereString", sql)))
                {
                    do
                    {
                        while (dr.Read())
                        {
                            result.Add(new PRLMessage(dr));
                        }
                    }
                    while (dr.NextResult());
                }
            }
            return result;
        }

        #endregion

        #region Publish Usage

        public FlightPassengerInfo GetFlightPassengerInfo(string sql)
        {
            FlightPassengerInfo result = new FlightPassengerInfo();
            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, sql, CommandType.Text))
                {
                    result.LDM = DTOHelper.CreateModelList<LDMMessage>(dr);

                    dr.NextResult();

                    result.PRL = DTOHelper.CreateModelList<PRLMessage>(dr);
                }
            }
            return result;
        }

        public List<DCSFile> GetSyncMessagesByMessageType(out List<PRLMessage> prlList, string messageType, int groupCount)
        {
            prlList = new List<PRLMessage>();
            List<DCSFile> result = new List<DCSFile>();

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetSyncMessagesByMessageTypeNew",
                    new SqlParameter("@MessageType", messageType),
                    new SqlParameter("@GroupCount", groupCount)))
                {
                    result = DTOHelper.CreateModelList<DCSFile>(dr);
                    dr.NextResult();
                    prlList = DTOHelper.CreateModelList<PRLMessage>(dr);
                }
            }

            return result;
        }

        public List<DCSFile> GetSyncPFSMessagesByMessageType(out List<PFSMessage> pfsList, string messageType, int groupCount)
        {
            pfsList = new List<PFSMessage>();
            List<DCSFile> result = new List<DCSFile>();

            using (SqlConnection conn = GetSqlConnection())
            {
                using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetSyncMessagesByMessageTypeNew",
                    new SqlParameter("@MessageType", messageType),
                    new SqlParameter("@GroupCount", groupCount)))
                {
                    result = DTOHelper.CreateModelList<DCSFile>(dr);
                    dr.NextResult();
                    pfsList = DTOHelper.CreateModelList<PFSMessage>(dr);
                    dr.NextResult();
                    List<PFSPassenger> passengerList = DTOHelper.CreateModelList<PFSPassenger>(dr);
                    foreach (var passengers in passengerList.GroupBy(p => p.MessageId))
                    {
                        pfsList.Single(pfs => pfs.MessageId == passengers.Key).PFSPassengers = passengers.ToList();
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 批量插入同步日志
        /// </summary>
        /// <param name="logList"></param>
        public void InsertSyncLog(List<ESBSyncLog> logList)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_InsertSyncLog",
                                          new SqlParameter("@listXml", SqlHelper.ConvertModelListToXML<ESBSyncLog>("e", logList)));

                conn.Close();

            }
        }

        #endregion

        #region SITAMessage

        public int InsertSourceMessage(SITAMessage sourceMessage)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter spSourceID = new SqlParameter("@SourceID", SqlDbType.Int);
                spSourceID.Direction = ParameterDirection.Output;
                spSourceID.Value = sourceMessage.SourceID;

                SqlHelper.ExecuteNonQuery(conn, "up_CreateSourceMessage",
                    spSourceID
                    , new SqlParameter("@RemoteLocation", sourceMessage.RemoteLocation)
                    , new SqlParameter("@RemoteID", sourceMessage.RemoteID)
                    , new SqlParameter("@SendClient", sourceMessage.SendClient)
                    , new SqlParameter("@ReviceClient", sourceMessage.ReviceClient)
                    , new SqlParameter("@MessageType", sourceMessage.MessageType)
                    , new SqlParameter("@SourceContent", sourceMessage.SourceContent)
                    , new SqlParameter("@TeleTime", sourceMessage.TeleTime)
                    , new SqlParameter("@Status", sourceMessage.Status)
                    , new SqlParameter("@DownLoadTime", sourceMessage.DownLoadTime)
                    , new SqlParameter("@ParseStartTime", sourceMessage.ParseStartTime)//<sourceMessage.DownLoadTime.AddYears(-1)?(object)DBNull.Value:(object)sourceMessage.ParseStartTime)
                    , new SqlParameter("@ParseFinishTime", sourceMessage.ParseFinishTime)// < sourceMessage.DownLoadTime.AddYears(-1) ? (object)DBNull.Value : (object)sourceMessage.ParseFinishTime)
                    , new SqlParameter("@ESBSyncTime", sourceMessage.ESBSyncTime)// < sourceMessage.DownLoadTime.AddYears(-1) ? (object)DBNull.Value : (object)sourceMessage.ESBSyncTime)
                   , new SqlParameter("@ParseErrorLog", sourceMessage.ParseErrorLog)
                    );

                return (int)spSourceID.Value;
            }
        }

        public void UpdateSourceMessageStatus(List<int> idList,FileStatus status)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateSourceMessage", new SqlParameter("@SourceIDs", SqlHelper.ConvertIdsToXML<int>("e", idList.ToArray())), new SqlParameter("@Status", status));
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sourceMessage"></param>
        public  void UpdateSourceMessage(SITAMessage sourceMessage)
        {
            using (SqlConnection conn =GetSqlConnection() )
            {
                SqlHelper.ExecuteNonQuery(conn, "up_UpdateSourceMessage",
                    new SqlParameter("@SourceID", sourceMessage.SourceID)
                    , new SqlParameter("@RemoteLocation", sourceMessage.RemoteLocation)
                    , new SqlParameter("@RemoteID", sourceMessage.RemoteID)
                    , new SqlParameter("@SendClient", sourceMessage.SendClient)
                    , new SqlParameter("@ReviceClient", sourceMessage.ReviceClient)
                    , new SqlParameter("@MessageType", sourceMessage.MessageType)
                    , new SqlParameter("@SourceContent", sourceMessage.SourceContent)
                    , new SqlParameter("@TeleTime", sourceMessage.TeleTime)
                    , new SqlParameter("@Status", sourceMessage.Status)
                    , new SqlParameter("@DownLoadTime", sourceMessage.DownLoadTime)
                    , new SqlParameter("@ParseStartTime", sourceMessage.ParseStartTime)
                    , new SqlParameter("@ParseFinishTime", sourceMessage.ParseFinishTime)
                    , new SqlParameter("@ESBSyncTime", sourceMessage.ESBSyncTime)
                    , new SqlParameter("@ParseErrorLog",sourceMessage.ParseErrorLog)
                );
            }

        }



        public int GetMaxSourceID(string monthDir)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                string sql = string.Format("select max(RemoteID) from [SourceMessage] where RemoteLocation='{0}'", monthDir);
                object ob = SqlHelper.ExecuteScalar(conn, sql, "1");
                {
                    try
                    {
                        return Convert.ToInt32(ob);
                    }
                    catch
                    {
                        return 0;
                    }
                }

            }
        }


        public TRChangeMessage InsertTRChangeMessage(TRChangeMessage tRChangeMessage)
        {
            using (SqlConnection conn =GetSqlConnection())
            {
                SqlParameter spMessageID = new SqlParameter("@MessageID", SqlDbType.UniqueIdentifier);
                spMessageID.Direction = ParameterDirection.Output;
                spMessageID.Value = tRChangeMessage.MessageID;

                SqlHelper.ExecuteNonQuery(conn, "up_CreateTRChangeMessage",
                    spMessageID
                    , new SqlParameter("@RefSourceID", tRChangeMessage.RefSourceID)
                    , new SqlParameter("@ChangeStartDate", tRChangeMessage.ChangeStartDate)
                    , new SqlParameter("@ChangeEndDate", tRChangeMessage.ChangeEndDate)
                    , new SqlParameter("@ChangeDay", tRChangeMessage.ChangeDay)
                    , new SqlParameter("@FlightNo", tRChangeMessage.FlightNo)
                    , new SqlParameter("@ChangeType", tRChangeMessage.ChangeType)
                    , new SqlParameter("@DeptAirport", tRChangeMessage.DeptAirport)
                    , new SqlParameter("@DeptTime", tRChangeMessage.DeptTime)
                    , new SqlParameter("@ArrAirport", tRChangeMessage.ArrAirport)
                    , new SqlParameter("@ArrTime", tRChangeMessage.ArrTime)
                    , new SqlParameter("@RealReason", tRChangeMessage.RealReason)
                );
                tRChangeMessage.MessageID = (Guid)spMessageID.Value;
                return tRChangeMessage;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="aCChangeMessage"></param>
        public ACChangeMessage InsertACChangeMessage(ACChangeMessage aCChangeMessage)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter spMessageID = new SqlParameter("@MessageID", SqlDbType.UniqueIdentifier);
                spMessageID.Direction = ParameterDirection.Output;
                spMessageID.Value = aCChangeMessage.MessageID;

                SqlHelper.ExecuteNonQuery(conn, "up_CreateACChangeMessage",
                    spMessageID
                    , new SqlParameter("@RefSourceID", aCChangeMessage.RefSourceID)
                    , new SqlParameter("@ChangeStartDate", aCChangeMessage.ChangeStartDate)
                    , new SqlParameter("@ChangeEndDate", aCChangeMessage.ChangeEndDate)
                    , new SqlParameter("@ChangeDay", aCChangeMessage.ChangeDay)
                    , new SqlParameter("@FlightNo", aCChangeMessage.FlightNo)
                    , new SqlParameter("@DeptAirport", aCChangeMessage.DeptAirport)
                    , new SqlParameter("@ArrAirport", aCChangeMessage.ArrAirport)
                    , new SqlParameter("@OldModel", aCChangeMessage.OldModel)
                    , new SqlParameter("@NewModel", aCChangeMessage.NewModel)
                    , new SqlParameter("@RealReason", aCChangeMessage.RealReason)
                );
                aCChangeMessage.MessageID = (Guid)spMessageID.Value;
                return aCChangeMessage;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cancleMessage"></param>
        public CancleMessage InsertCancleMessage(CancleMessage cancleMessage)
        {
            using (SqlConnection conn = GetSqlConnection())
            {
                SqlParameter spMessageID = new SqlParameter("@MessageID", SqlDbType.UniqueIdentifier);
                spMessageID.Direction = ParameterDirection.Output;
                spMessageID.Value = cancleMessage.MessageID;

                SqlHelper.ExecuteNonQuery(conn, "up_CreateCancleMessage",
                    spMessageID
                    , new SqlParameter("@RefSourceID", cancleMessage.RefSourceID)
                    , new SqlParameter("@ChangeStartDate", cancleMessage.ChangeStartDate)
                    , new SqlParameter("@ChangeEndDate", cancleMessage.ChangeEndDate)
                    , new SqlParameter("@ChangeDay", cancleMessage.ChangeDay)
                    , new SqlParameter("@FlightNo", cancleMessage.FlightNo)
                    , new SqlParameter("@DeptAirport", cancleMessage.DeptAirport)
                    , new SqlParameter("@ArrAirport", cancleMessage.ArrAirport)
                    , new SqlParameter("@ArrangeComment", cancleMessage.ArrangeComment)
                    , new SqlParameter("@RealReason", cancleMessage.RealReason)
                );
                cancleMessage.MessageID = (Guid)spMessageID.Value;
                return cancleMessage;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="messageType"></param>
        /// <param name="rowSize"></param>
        /// <param name="selectStatus"></param>
        /// <param name="updateStatus"></param>
        /// <returns></returns>
        public List<SITAMessage> GetSourceMessageToParse(string messageType, int rowSize, int selectStatus, int updateStatus)
        {
            List<SITAMessage> result = new List<SITAMessage>();
            using (SqlConnection conn = GetSqlConnection())
            {
                using (IDataReader dr = SqlHelper.ExecuteReader(conn, "up_GetSourceMessageToParse", new SqlParameter("@MessageType", messageType),
                                                                                               new SqlParameter("@RowSize", rowSize),
                                                                                               new SqlParameter("@SelectStatus", selectStatus),
                                                                                               new SqlParameter("@UpdateStatus", updateStatus)))
                {
                    result = DTOHelper.CreateModelList<SITAMessage>(dr);
                    dr.Close();
                }
            }

            return result;
        }


        #endregion
    }
}
